package newsDAO;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;

public class NewsDAO {
	public Page<News> search(int pageNo) {
		Page<News> p = new Page<News>();
		List<News> list = new ArrayList<News>();
		PreparedStatement st = null;
		ResultSet rs = null;
		String sql = "select * from news  order by ncreateDate DESC limit ?,?";
		Connection con = null;
		con = NewsJDBCUtils.getConnection();
		try {
			st = con.prepareStatement(sql);
			st.setInt(1, (pageNo - 1) * Page.PAGESIZE);
			st.setInt(2, p.PAGESIZE);
			rs = st.executeQuery();
			while (rs.next()) {
				News ns = new News();
				ns.setNid(rs.getLong("nid"));
				ns.setNtid(rs.getLong("ntid"));
				ns.setNtitle(rs.getString("ntitle"));
				ns.setNauthor(rs.getString("nauthor"));
				ns.setNcreateDate(rs.getString("ncreateDate"));
				ns.setNpicPath(rs.getString("npicPath"));
				ns.setNcontent(rs.getString("ncontent"));
				ns.setNmodifyDate(rs.getString("nmodifyDate"));
				ns.setNsummary(rs.getString("nsummary"));
				list.add(ns);
			}
			p.setDatas(list);
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			NewsJDBCUtils.closeAll(st, rs, null);
		}
		String sql2 = "select count(*) from news";
		int total = 0;
		try {
			st = con.prepareStatement(sql2);

			rs = st.executeQuery();
			if (rs.next()) {
				total = rs.getInt(1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			NewsJDBCUtils.closeAll(st, rs, con);
		}
		p.setTotalLines(total);

		return p;
	}

	public News get(long id) {
		PreparedStatement st = null;
		ResultSet rs = null;
		String sql = "select * from news where nid=?";
		Connection con = null;
		con = NewsJDBCUtils.getConnection();
		News ns = null;
		try {
			st = con.prepareStatement(sql);
			st.setLong(1, id);
			rs = st.executeQuery();
			if (rs.next()) {
				ns = new News();
				ns.setNid(rs.getLong("nid"));
				ns.setNtid(rs.getLong("ntid"));
				ns.setNtitle(rs.getString("ntitle"));
				ns.setNauthor(rs.getString("nauthor"));
				ns.setNcreateDate(rs.getString("ncreateDate"));
				ns.setNpicPath(rs.getString("npicPath"));
				ns.setNcontent(rs.getString("ncontent"));
				ns.setNmodifyDate(rs.getString("nmodifyDate"));
				ns.setNsummary(rs.getString("nsummary"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			NewsJDBCUtils.closeAll(st, rs, con);
		}
		return ns;
	}

	public void add(News n) {
		PreparedStatement st = null;
		Connection con = null;
		ResultSet rs = null;
		int r = 0;
		con = NewsJDBCUtils.getConnection();
		String sql = "insert into news (ntid,ntitle,nauthor,ncreateDate,npicPath,ncontent,nmodifyDate,nsummary) values (?,?,?,?,?,?,?,?)";
		try {
			st = con.prepareStatement(sql);
			int index = 1;
			st.setLong(index++, n.getNtid());
			st.setString(index++, n.getNtitle());
			st.setString(index++, n.getNauthor());
			SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
			java.util.Date date = sdf.parse(n.getNcreateDate());
			java.sql.Date ncreatedate = new java.sql.Date(date.getTime());
			st.setDate(index++, ncreatedate);
			st.setString(index++, n.getNpicPath());
			st.setString(index++, n.getNcontent());
			date = sdf.parse(n.getNmodifyDate());
			java.sql.Date nmodifyDate = new java.sql.Date(date.getTime());
			st.setDate(index++, nmodifyDate);
			st.setString(index++, n.getNsummary());
			r = st.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			NewsJDBCUtils.closeAll(st, rs, con);
		}
	}

	public void del(long id) {
		int i = 0;
		String sql = "delete from `news` where ntid=?";
		Connection con = NewsJDBCUtils.getConnection();
		PreparedStatement st = null;
		ResultSet rs = null;
		try {
			st = con.prepareStatement(sql);
			st.setLong(1, id);
			i = st.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			NewsJDBCUtils.closeAll(st, rs, con);
		}
	}

	public void update(News n) {
		String sql = "update `news` set ntitle=?,nauthor=?,ncreateDate=?,npicPath=?,ncontent=?,nmodifyDate=?,nsummary=? where ntid=?";
		int i = 0;
		Connection con = NewsJDBCUtils.getConnection();
		PreparedStatement st = null;
		ResultSet rs = null;
		try {
			st = con.prepareStatement(sql);
			int index = 1;
			st.setString(index++, n.getNtitle());
			st.setString(index++, n.getNauthor());
			SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
			java.util.Date date = sdf.parse(n.getNcreateDate());
			java.sql.Date ncreatedate = new java.sql.Date(date.getTime());
			st.setDate(index++, ncreatedate);
			st.setString(index++, n.getNpicPath());
			st.setString(index++, n.getNcontent());
			date = sdf.parse(n.getNmodifyDate());
			java.sql.Date nmodifyDate = new java.sql.Date(date.getTime());
			st.setDate(index++, nmodifyDate);
			st.setString(index++, n.getNsummary());
			st.setLong(index++, n.getNtid());
			i = st.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			NewsJDBCUtils.closeAll(st, rs, con);
		}
	}

	public List<News> findTop(long cid) {
		List<News> list = new ArrayList<News>();
		String sql = "select * from `news` WHERE ntid=? ORDER BY ncreateDate DESC LIMIT 5 ";

		Connection con = NewsJDBCUtils.getConnection();
		PreparedStatement st = null;
		ResultSet rs = null;
		try {
			st = con.prepareStatement(sql);
			st.setLong(1, cid);
			rs = st.executeQuery();
			while (rs.next()) {
				News ns = new News();
				ns.setNtid(rs.getInt("ntid"));
				ns.setNtitle(rs.getString("ntitle"));
				ns.setNauthor(rs.getString("nauthor"));
				ns.setNcreateDate(rs.getString("ncreateDate"));
				ns.setNpicPath(rs.getString("npicPath"));
				ns.setNcontent(rs.getString("ncontent"));
				ns.setNmodifyDate(rs.getString("nmodifyDate"));
				ns.setNsummary(rs.getString("nsummary"));
				list.add(ns);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			NewsJDBCUtils.closeAll(st, rs, con);
		}
		return list;
	}

}
